IF COL_LENGTH('GlobalPaymentsTransaction', 'UId') IS NULL
BEGIN
ALTER TABLE GlobalPaymentsTransaction 
ADD UId NCHAR(50) NULL
END
GO
IF COL_LENGTH('Category', 'SlideShowId') IS NULL
BEGIN

	ALTER TABLE Category 
	ADD SlideShowId INT NULL
	
	ALTER TABLE [dbo].[Category]  WITH CHECK ADD  CONSTRAINT [FK_Category_Slideshow] FOREIGN KEY([SlideshowId])
	REFERENCES [dbo].[Slideshow] ([Id])
	
	ALTER TABLE [dbo].[Category] CHECK CONSTRAINT [FK_Category_Slideshow]
END

GO
IF NOT EXISTS (SELECT 1 FROM Setting Where Name='apipaymentssettings.merchPass_GetUID_Url')
BEGIN
INSERT INTO Setting(Name,Value)
VALUES('apipaymentssettings.merchPass_GetUID_Url','https://direct.sandbox-gtpaysecure.net/direct/services/request/init/?action=buildForm&merchPass=')
END

IF NOT EXISTS (SELECT 1 FROM Setting Where Name='apipaymentssettings.ifarmeUrl')
BEGIN
INSERT INTO Setting(Name,Value)
VALUES('apipaymentssettings.ifarmeUrl','http://spotshubtest3.azurewebsites.net/common/getform?{0}')
END

IF NOT EXISTS (SELECT 1 FROM Setting Where Name='apipaymentssettings.merchPass')
BEGIN
INSERT INTO Setting(Name,Value)
VALUES('apipaymentssettings.merchPass','gt694878820888SB')
END

IF NOT EXISTS (SELECT 1 FROM Setting Where Name='apipaymentssettings.fromUrl')
BEGIN
INSERT INTO Setting(Name,Value)
VALUES('apipaymentssettings.fromUrl','https://safe.sandbox-gtpaysecure.net/hpf/1_1/?uID=')
END

IF NOT EXISTS (SELECT 1 FROM Setting Where Name='globalpaymentssettings.refundurl')
BEGIN
INSERT INTO Setting(Name,Value)
VALUES('globalpaymentssettings.refundurl','https://direct.sandbox-gtpaysecure.net/direct/services/reversal/')
END

IF NOT EXISTS(SELECT 1 FROM ProductAttribute WHERE  NAME ='GiftMessage')
BEGIN
	INSERT INTO ProductAttribute(Name) VALUES('GiftMessage')
END

IF NOT EXISTS(SELECT 1 FROM ProductAttribute WHERE  NAME ='HaveGift')
BEGIN
	INSERT INTO ProductAttribute(Name) VALUES('HaveGift')
END

GO
IF NOT EXISTS (select * from sys.tables where name='ClubMembership')
BEGIN
	CREATE TABLE [dbo].[ClubMembership](
		[Id] [int] IDENTITY(1,1) NOT NULL,
		[CustomerRoleId] [int] NOT NULL,
		[Name] [nvarchar](50) NOT NULL,
		[SystemName] [nvarchar](50) NOT NULL,
		[Description] [nvarchar](4000) NULL,
		[LocalShipmentFee] [decimal](18, 2) NOT NULL,
		[MembershipDuration] [int] NOT NULL,
		[ProductVariantId] [int] NULL
	 CONSTRAINT [PK_ClubMembership] PRIMARY KEY CLUSTERED 
	(
		[Id] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
	)


	ALTER TABLE [dbo].[ClubMembership]  WITH CHECK ADD  CONSTRAINT [FK_ClubMembership_CustomerRole] FOREIGN KEY([CustomerRoleId])
	REFERENCES [dbo].[CustomerRole] ([Id])


	ALTER TABLE [dbo].[ClubMembership] CHECK CONSTRAINT [FK_ClubMembership_CustomerRole]
END

GO
IF NOT EXISTS(select 1 from sys.tables where name='ClubMembershipHistory')
BEGIN
	CREATE TABLE [dbo].[ClubMembershipHistory](
		[Id] [int] IDENTITY(1,1) NOT NULL,
		[CustomerId] [int] NOT NULL,
		[MembershipId] [int] NOT NULL,
		[CreatedDateTime] [DateTime] Not NULL
		CONSTRAINT [PK_ClubMembershipHistory] PRIMARY KEY CLUSTERED
		(
			[Id] ASC
		)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
	)
	
	ALTER TABLE [dbo].[ClubMembershipHistory] WITH CHECK ADD CONSTRAINT [FK_ClubMembershipHistory_Customer] FOREIGN KEY([CustomerId])
	REFERENCES [dbo].[Customer] ([Id])

	
	ALTER TABLE [dbo].[ClubMembershipHistory] WITH CHECK ADD CONSTRAINT [FK_ClubMembershipHistory_Membership] FOREIGN KEY([MembershipId])
	REFERENCES [dbo].[ClubMembership] ([Id])
END

GO
IF COL_LENGTH('Customer_CustomerRole_Mapping', 'StartDate') IS NULL
BEGIN
	ALTER TABLE Customer_CustomerRole_Mapping 
	ADD StartDate DateTime NULL
END

GO
IF COL_LENGTH('Customer_CustomerRole_Mapping', 'EndDate') IS NULL
BEGIN
	ALTER TABLE Customer_CustomerRole_Mapping 
	ADD EndDate DateTime NULL
END

GO
IF NOT EXISTS(SELECT 1 FROM [dbo].[CustomerRole] WHERE SystemName='DreamFactoryUser')
BEGIN
	INSERT INTO [dbo].[CustomerRole]
           ([Name]
           ,[FreeShipping]
           ,[TaxExempt]
           ,[Active]
           ,[IsSystemRole]
           ,[SystemName])
     VALUES           
		   ('Dream Factory User',1,0,1,0,'DreamFactoryUser')
END

DECLARE @dfcRoleId INT
SELECT @dfcRoleId = ID from [dbo].[CustomerRole] WHERE SystemName='DreamFactoryUser'
SELECT * FROM [dbo].[CustomerRole]
IF NOT EXISTS(SELECT 1 FROM [dbo].[ClubMembership] WHERE SystemName='DreamFactoryMembership')
BEGIN
	INSERT INTO [dbo].[ClubMembership]
           ([CustomerRoleId]
           ,[Name]
           ,[SystemName]
           ,[Description]
           ,[LocalShipmentFee]
		   ,[MembershipDuration]
           ,[ProductVariantId])
     VALUES
           (@dfcRoleId, 'Dream Factory Membership','DreamFactoryMembership','Dream Factory Membership, valid for 1 month',0,30, NULL),
		   (@dfcRoleId, 'Dream Factory Membership','DreamFactoryMembership','Dream Factory Membership, valid for 1 year',0,365, NULL)   
END

DECLARE @DFCClubProdId INT
IF NOT EXISTS (SELECT 1 FROM [dbo].[Product] WHERE Name='Dream Factory Membership')
BEGIN
	INSERT INTO [dbo].[Product]
			   ([Name]
			   ,[ShortDescription]
			   ,[FullDescription]
			   ,[AdminComment]
			   ,[ProductTemplateId]
			   ,[ShowOnHomePage]
			   ,[MetaKeywords]
			   ,[MetaDescription]
			   ,[MetaTitle]
			   ,[AllowCustomerReviews]
			   ,[ApprovedRatingSum]
			   ,[NotApprovedRatingSum]
			   ,[ApprovedTotalReviews]
			   ,[NotApprovedTotalReviews]
			   ,[SubjectToAcl]
			   ,[Published]
			   ,[Deleted]
			   ,[CreatedOnUtc]
			   ,[UpdatedOnUtc]
			   ,[StoreId])
		 VALUES
			   ('Dream Factory Membership','Dream Factory Membership','<p>Dream Factory Membership</p>',NULL,1,0,NULL,NULL,NULL,0,0,0,0,0,0,0,0,GETUTCDATE(), GETUTCDATE(),1)


	SELECT @DFCClubProdId = Id from [dbo].[Product] WHERE Name = 'Dream Factory Membership'
	INSERT INTO [dbo].[ProductVariant]
			   ([ProductId]
			   ,[Name]
			   ,[Sku]
			   ,[Description]
			   ,[AdminComment]
			   ,[ManufacturerPartNumber]
			   ,[Gtin]
			   ,[IsGiftCard]
			   ,[GiftCardTypeId]
			   ,[RequireOtherProducts]
			   ,[RequiredProductVariantIds]
			   ,[AutomaticallyAddRequiredProductVariants]
			   ,[IsDownload]
			   ,[DownloadId]
			   ,[UnlimitedDownloads]
			   ,[MaxNumberOfDownloads]
			   ,[DownloadExpirationDays]
			   ,[DownloadActivationTypeId]
			   ,[HasSampleDownload]
			   ,[SampleDownloadId]
			   ,[HasUserAgreement]
			   ,[UserAgreementText]
			   ,[IsRecurring]
			   ,[RecurringCycleLength]
			   ,[RecurringCyclePeriodId]
			   ,[RecurringTotalCycles]
			   ,[IsShipEnabled]
			   ,[IsFreeShipping]
			   ,[AdditionalShippingCharge]
			   ,[IsTaxExempt]
			   ,[TaxCategoryId]
			   ,[ManageInventoryMethodId]
			   ,[StockQuantity]
			   ,[DisplayStockAvailability]
			   ,[DisplayStockQuantity]
			   ,[MinStockQuantity]
			   ,[LowStockActivityId]
			   ,[NotifyAdminForQuantityBelow]
			   ,[BackorderModeId]
			   ,[AllowBackInStockSubscriptions]
			   ,[OrderMinimumQuantity]
			   ,[OrderMaximumQuantity]
			   ,[AllowedQuantities]
			   ,[DisableBuyButton]
			   ,[DisableWishlistButton]
			   ,[AvailableForPreOrder]
			   ,[CallForPrice]
			   ,[Price]
			   ,[OldPrice]
			   ,[ProductCost]
			   ,[SpecialPrice]
			   ,[SpecialPriceStartDateTimeUtc]
			   ,[SpecialPriceEndDateTimeUtc]
			   ,[CustomerEntersPrice]
			   ,[MinimumCustomerEnteredPrice]
			   ,[MaximumCustomerEnteredPrice]
			   ,[HasTierPrices]
			   ,[HasDiscountsApplied]
			   ,[Weight]
			   ,[Length]
			   ,[Width]
			   ,[Height]
			   ,[PictureId]
			   ,[AvailableStartDateTimeUtc]
			   ,[AvailableEndDateTimeUtc]
			   ,[Published]
			   ,[Deleted]
			   ,[DisplayOrder]
			   ,[CreatedOnUtc]
			   ,[UpdatedOnUtc]
			   ,[RegionId]
			   ,[BranchId]
			   ,[IsOpenBoxProductVariant]
			   ,[BoundProductVariantId])
		 VALUES           
	(@DFCClubProdId,'1 Month Membership',NULL,'Membership that lasts one month starting from your purchase immidiately', NULL,NULL,NULL,	0,	0,	0,NULL	,0,	0	,0,1,10,NULL,1,0,0,0,NULL,0,100,0	,10,	0,	0,	0.0000,	1,0,	0,	10000,	0,	0,	0,	0,	1,	0,	0,	1,	1,	NULL,	0,	0,	0,	0,	4.0000,	4.0000,	0,	0,	NULL,	NULL,	0,	0.0000,	1000.0000,	0,	0,	0.0000,	0.0000,	0.0000, 0.0000,	0,	NULL,	NULL,	1,	0,	1,	GETUTCDATE(),GETUTCDATE(), 0,	0, 0,	NULL),
	(@DFCClubProdId,'1 Year Membership', NULL,'Membership that lasts one year starting from your purchase immidiately',  NULL,NULL,	NULL,	0,	0,	0,NULL	,0,	0	,0,1,10,NULL,1,0,0,0,NULL,0,100,0	,10,	0,	0,	0.0000,	1,0,	0,	10000,	0,	0,	0,	0,	1,	0,	0,	1,	1,	NULL,	0,	0,	0,	0,	20.0000,20.0000,0.0000,	0.0000, NULL,	NULL,	0,	0.0000, 1000.0000,	0,	0,	0.0000, 0.0000,	0.0000,	0.0000,	0,	NULL,	NULL,	1,	0,	1,	GETUTCDATE(),GETUTCDATE(),	0,	0,	0	,NULL)
END

DECLARE @DFCProductVariantId INT
SELECT @DFCProductVariantId = Id from [dbo].[ProductVariant] WHERE ProductId = @DFCClubProdId AND Price = 20

UPDATE [dbo].[ClubMembership] SET ProductVariantId = @DFCProductVariantId 
WHERE [CustomerRoleId] = @dfcRoleId and MembershipDuration = 365

SELECT @DFCProductVariantId = Id from [dbo].[ProductVariant] WHERE ProductId = @DFCClubProdId AND Price = 4

UPDATE [dbo].[ClubMembership] SET ProductVariantId = @DFCProductVariantId 
WHERE [CustomerRoleId] = @dfcRoleId and MembershipDuration = 30

GO
IF COL_LENGTH('Currency', 'CountryCode') IS NULL
BEGIN
ALTER TABLE Currency 
ADD CountryCode NCHAR(5) NULL
END

GO
IF  EXISTS(SELECT 1 FROM Currency WHERE Name = 'Canadian Dollar' AND CountryCode is null)
BEGIN
	UPDATE Currency SET CountryCode='CA' WHERE Name = 'Canadian Dollar';
END

IF  EXISTS(SELECT 1 FROM Currency WHERE Name = 'US Dollar' AND CountryCode is null)
BEGIN
	UPDATE Currency SET CountryCode='US' WHERE Name = 'US Dollar';
END

---add by Jackie on June 22
GO
IF NOT EXISTS (select * from sys.tables where name='ThirdPartyShippingOption')
BEGIN
	CREATE TABLE [dbo].[ThirdPartyShippingOption](
		[Id] [int] IDENTITY(1,1) NOT NULL,
		[PackageCount] [int] NOT NULL,
		[Rate] [decimal](18, 2) NOT NULL,
		[Name] [nvarchar](50) NOT NULL,
		[CustomerEmail] [nvarchar](50) NOT NULL,
		[ShippingAddress] [nvarchar](150) NOT NULL,
		[BillingAddress] [nvarchar](150) NOT NULL,
		[ShippingRateComputationMethodSystemName] [nvarchar](50) NOT NULL,
		[CreatedOnUTC] [DateTime] NOT NULL,
		CONSTRAINT [PK_ThirdPartyShippingOption] PRIMARY KEY CLUSTERED 
		(
			[Id] ASC
		)
	)
END 

---add by Jackie on June 23
GO
IF COL_LENGTH('ThirdPartyShippingOption', 'OrderId') IS NULL
BEGIN
	ALTER TABLE ThirdPartyShippingOption
	ADD OrderId int NOT NULL FOREIGN KEY REFERENCES [Order](Id)
END

GO
--- Global Payments Sandbox API and Logistics Improvement by Daniel
IF NOT EXISTS (SELECT 1 FROM Setting Where Name='globalpaymentssettings.enablesandbox')
BEGIN
INSERT INTO Setting(Name,Value)
VALUES('globalpaymentssettings.enablesandbox','False')
END

IF NOT EXISTS (SELECT 1 FROM Setting Where Name='globalpaymentssettings.logisticscssurl')
BEGIN
INSERT INTO Setting(Name,Value)
VALUES('globalpaymentssettings.logisticscssurl','')
END

IF NOT EXISTS (SELECT 1 FROM Setting Where Name='globalpaymentssettings.logisticscallbackurl')
BEGIN
INSERT INTO Setting(Name,Value)
VALUES('globalpaymentssettings.logisticscallbackurl','')
END

IF NOT EXISTS (SELECT 1 FROM Setting Where Name='globalpaymentssettings.sandboxapitoken')
BEGIN
INSERT INTO Setting(Name,Value)
VALUES('globalpaymentssettings.sandboxapitoken','c341fa747b8a79d49f6ec94fb25bd15f')
END

IF NOT EXISTS (SELECT 1 FROM Setting Where Name='globalpaymentssettings.sandboxprofileid')
BEGIN
INSERT INTO Setting(Name,Value)
VALUES('globalpaymentssettings.sandboxprofileid','gt694878820888SB')
END

IF NOT EXISTS (SELECT 1 FROM Setting Where Name='globalpaymentssettings.sandboxdirectserviceurl')
BEGIN
INSERT INTO Setting(Name,Value)
VALUES('globalpaymentssettings.sandboxdirectserviceurl','https://direct.sandbox-gtpaysecure.net/direct/services/')
END

IF NOT EXISTS (SELECT 1 FROM Setting Where Name='globalpaymentssettings.sandboxformurl')
BEGIN
INSERT INTO Setting(Name,Value)
VALUES('globalpaymentssettings.sandboxformurl','https://safe.sandbox-gtpaysecure.net/hpf/1_1/')
END

IF NOT EXISTS (SELECT 1 FROM Setting Where Name='mediasettings.homepageslideshow')
BEGIN
INSERT INTO [dbo].[Setting] ([Name],[Value])
	  VALUES ('mediasettings.homepageslideshow','HomepageTopSlideshowCarousel')
END

IF NOT EXISTS (SELECT 1 FROM Setting Where Name='customersettings.passwordRecoveryUrl')
BEGIN
INSERT INTO [dbo].[Setting] ([Name],[Value])
	  VALUES ('customersettings.passwordRecoveryUrl','https://spotshubtest3.azurewebsites.net/passwordrecovery')
END

